﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;
using Newtonsoft.Json;
using CRMEntity;

namespace CRMBiz
{
    public static class CategoryNodeHandler
    {
        public static bool collepse = false;

        public static List<Template> templatesListResult = null;

        public static int saveCategory(string tableName, int CategoryID, string CategoryName, int ParentId)
        {
            int flag = 0;
            String strQuery = string.Empty;

            try
            {
                if (CategoryID <= 0)
                    strQuery = " Insert into " + tableName + "(CategoryName,ParentId) values ('" + CategoryName + "'," + ParentId + ")";
                else
                    strQuery = " Update " + tableName + " SET CategoryName='" + CategoryName + "', ParentId=" + ParentId + " where CategoryId=" + CategoryID;
                DBhelper.ExecuteNonQuery(strQuery);
                flag = 1;
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            return flag;
        }

        private static bool allowDeleteCategory(int categoryid, string tableName)
        {
            bool flag = false;
            try
            {
                string strquery = " select count(*) as total from " + tableName +  "  where CategoryId=" + categoryid;
                int count = Convert.ToInt32(DBhelper.ExecuteScalar(strquery));
                if (count == 0)
                    flag = true;
                else
                    flag = false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        public static int deleteCategory(int categoryid, string tableName)
        {
            int flag = 0;
            String strQuery = string.Empty;
            string templateTable=tableName.Replace("category","");
            
            try
            {
                int cnt = Convert.ToInt32(DBhelper.ExecuteScalar("select count(*) from " + tableName + " where ParentId=" + categoryid)); // Check Category has no child Category
                if (cnt == 0) 
                {
                    
                    if (allowDeleteCategory(categoryid, templateTable))
                    {
                        strQuery = " Delete from " + tableName + " Where CategoryId=" + categoryid;
                        DBhelper.ExecuteNonQuery(strQuery);
                        flag = 1; // operation Successful
                    }
                    else
                        flag = -1; // Invalid Delete; Category has one or more Template
                }
                else
                    flag = -2;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;

        }
        public static string getParentcategory(int categoryid,string tablenName)
        {
            string parentCategory=string.Empty;
            try
            {
                string strQuery = "Select ParentId from " + tablenName + " Where CategoryId=" + categoryid;
                parentCategory = DBhelper.ExecuteScalar(strQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return parentCategory;
        }
        /// <summary>
        /// Get Email Template Content
        /// </summary>
        /// <param name="emailId"></param>
        /// <returns></returns>
        public static DataTable GetEmailTemplateContent(int emailId)
        {

            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailId", emailId),
                };

                string sbSql = "select TemplateFormat,emailsubject,CategoryId from crm_emailtemplate where EmailId=@EmailId";
                dt = DBhelper.ExecuteDataset(sbSql, para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        /// <summary>
        /// Get SMS Template Content
        /// </summary>
        /// <param name="smsTemplateId"></param>
        /// <returns></returns>
        public static DataTable GetSMSTemplateContent(int smsTemplateId)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@SMSTemplateID", smsTemplateId),
                };

                string sbSql = "select TemplateFormat,CategoryId from crm_smstemplate where SMSTemplateID=@SMSTemplateID";
                dt = DBhelper.ExecuteDataset(sbSql, para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// Get Treeview Nodes
        /// </summary>
        /// <returns></returns>


        public static List<CategoryNode> GetCategoryNodesList(bool isSMS, bool isOnlyCategory,bool isDual)
        {
            TemplateCategory templateCategory = null;
            List<TemplateCategory> templateCategoriesList = new List<TemplateCategory>();
            string categoryTableName = null;
            int currentCategoryId = 0;


            try
            {
                if (isDual)
                    categoryTableName = "crm_dualtemplatecategory";
                else
                {
                    if (isSMS)
                        categoryTableName = "crm_smstemplatecategory";
                    else
                        categoryTableName = "crm_emailtemplatecategory";
                }

                if (isOnlyCategory == false)
                {
                  getTemplatesList(isSMS);
                }

                string sql = "SELECT CategoryId,CategoryName,ParentId FROM " + categoryTableName;

                DataTable dt = DBhelper.ExecuteDataset(sql).Tables[0];

                foreach (DataRow dr in dt.Rows)
                {
                    templateCategory = new TemplateCategory();
                    templateCategory.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                    templateCategory.CategoryName = dr["CategoryName"].ToString().Trim();
                    templateCategory.ParentId = Convert.ToInt16(dr["ParentId"].ToString().Trim());
                    templateCategoriesList.Add(templateCategory);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            var root = from r in templateCategoriesList
                       where r.ParentId == 0
                       select r;

            currentCategoryId = root.First().CategoryId;
            List<CategoryNode> rootnodes = new List<CategoryNode>();
            CategoryNode rootnode = new CategoryNode();
            rootnode.id = currentCategoryId;
            rootnode.text = root.First().CategoryName;
            if (isOnlyCategory == false)
                rootnode.children = GetChildrenNodes(templateCategoriesList, currentCategoryId, collepse);
            else
                rootnode.children = GetChildren(templateCategoriesList, currentCategoryId);

            if (isOnlyCategory == false)
            {
                List<CategoryNode> rootTemplate = new List<CategoryNode>();
                rootTemplate = GetBottomNodeContents(currentCategoryId);
                //Template childNode = null;
                foreach (CategoryNode category in rootTemplate)
                {
                  rootnode.children.Add(category);
                }
            }
           
            rootnodes.Add(rootnode);
           
            return rootnodes;
        }
        /// <summary>
        /// Get children Nodes
        /// </summary>
        /// <param name="categoriesList"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        private static List<CategoryNode> GetChildrenNodes(List<TemplateCategory> categoriesList, int parentId,bool isCollepse)
        {
            List<CategoryNode> childrenNodesList = new List<CategoryNode>();
            CategoryNode childNode = null;
            int currentCategoryId = 0;

            var childrenCategories = from r in categoriesList
                                     where r.ParentId == parentId
                                     select r;

            foreach (TemplateCategory category in childrenCategories)
            {
                childNode = new CategoryNode();
                currentCategoryId = category.CategoryId;
                childNode.id = currentCategoryId;
                childNode.text = category.CategoryName;

                if (isCollepse == true)
                    childNode.state = "closed";
                else
                    childNode.state = "open";

                childNode.children = GetChildrenNodes(categoriesList, category.CategoryId, true);
                childrenNodesList.Add(childNode);
            }
            foreach (CategoryNode category in childrenNodesList)
            {
                List<CategoryNode> rootTemplate = new List<CategoryNode>();
                rootTemplate = GetBottomNodeContents(category.id);
               
                foreach (CategoryNode template in rootTemplate)
                {
                    category.children.Add(template);
                }
                if (category.children.Count == 0)
                {
                    category.id = -1;
                    category.state = "open";
                    category.iconCls = "icon-folder";
                }
            }

            return childrenNodesList;
        }
        private static List<CategoryNode> GetChildren(List<TemplateCategory> categoriesList, int parentId)
        {
            List<CategoryNode> childrenNodesList = new List<CategoryNode>();
            CategoryNode childNode = null;
            int currentCategoryId = 0;

            var childrenCategories = from r in categoriesList
                                     where r.ParentId == parentId
                                     select r;
        
            foreach (TemplateCategory category in childrenCategories)
            {
                childNode = new CategoryNode();
                currentCategoryId = category.CategoryId;
                childNode.id = currentCategoryId;
                childNode.text = category.CategoryName;
             
                childNode.children = GetChildren(categoriesList, category.CategoryId);

                if (childNode.children.Count == 0)
                    childNode.iconCls = "icon-folder";
                childrenNodesList.Add(childNode);
            }
            foreach (CategoryNode category in childrenNodesList)
            {
                if (category.children.Count == 0)
                {
                    category.state = "open";
                    category.iconCls = "icon-folder";
                }

            }
            return childrenNodesList;
        }
        /// <summary>
        /// Get contents of bottom Node
        /// </summary>
        /// <param name="currentCategoryId"></param>
        /// <returns></returns>
        private static List<CategoryNode> GetBottomNodeContents(int currentCategoryId)
        {
            List<CategoryNode> bottomNodesList = new List<CategoryNode>();

            CategoryNode node = null;

            var result = from e in templatesListResult
                         where e.CategoryId == currentCategoryId
                         select e;

            foreach (Template e in result)
            {
                node = new CategoryNode();
                node.id = e.TemplateId;
                node.text = e.TemplateTitle;
                bottomNodesList.Add(node);
            }

            return bottomNodesList;
        }
         
        /// <summary>
        /// Get templates list
        /// </summary>
        /// <param name="templateTableName"></param>
        /// 

        private static void getTemplatesList(bool isSMS)
        {
            Template template = null;
            templatesListResult = new List<Template>();
            string sbSql = null;
            try
            {
                if (isSMS)
                {
                    sbSql = "Select SMSTemplateID,SMSTemplate,CategoryId From crm_smstemplate";
                }
                else
                {
                    sbSql = "Select EmailId,EmailTemplate,CategoryId From crm_emailtemplate";
                }

                DataTable emailTemplateTable = DBhelper.ExecuteDataset(sbSql).Tables[0];

                if (isSMS)
                {
                    foreach (DataRow dr in emailTemplateTable.Rows)
                    {
                        template = new Template();
                        template.TemplateId = Convert.ToInt16(dr["SMSTemplateID"].ToString().Trim());
                        template.TemplateTitle = dr["SMSTemplate"].ToString().Trim();
                        template.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                        templatesListResult.Add(template);
                    }
                }
                else
                {
                    foreach (DataRow dr in emailTemplateTable.Rows)
                    {
                        template = new Template();
                        template.TemplateId = Convert.ToInt16(dr["EmailId"].ToString().Trim());
                        template.TemplateTitle = dr["EmailTemplate"].ToString().Trim();
                        template.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                        templatesListResult.Add(template);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
